package SpdgBI.Common;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Udfs {
    public static List GetTableInfo(String TblName){
        Connection connBi = null;
        PreparedStatement stmtTbl = null;
        StringBuffer strSqlIns = null;
        List lstResult = null;
        Map mapOfSqlInsert = new HashMap(1);
        try {
            connBi = DBUtils.getConnection("SPDGBI");
            stmtTbl = connBi.prepareStatement("select COLUMN_NAME,DATA_TYPE from user_tab_columns where Table_Name=? ORDER BY COLUMN_ID");
            stmtTbl.setString(1,TblName);
            ResultSet rsTbl = stmtTbl.executeQuery();
            lstResult = ExtractData(rsTbl);
            strSqlIns = new StringBuffer();
            strSqlIns.append("INSERT INTO ");
            strSqlIns.append(TblName);
            strSqlIns.append("(");
            for (int i = 0; i < lstResult.size(); i++) {
                strSqlIns.append(((Map)lstResult.get(i)).get("COLUMN_NAME").toString());
                strSqlIns.append(",");
            }
            strSqlIns.delete(strSqlIns.length()-1,strSqlIns.length());
            strSqlIns.append(")VALUES (");
            for (int i = 0; i < lstResult.size(); i++) {
                strSqlIns.append("?,");
            }
            strSqlIns.delete(strSqlIns.length()-1,strSqlIns.length());
            strSqlIns.append(")");
            mapOfSqlInsert.put("sqlins",strSqlIns.toString());
            lstResult.add(mapOfSqlInsert);

        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            DBUtils.close(connBi);
            return lstResult;
        }
    }

    /**
     * 通用取结果方案,返回list
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    public static List ExtractData(ResultSet rs) throws SQLException {
        ResultSetMetaData md = rs.getMetaData();
        int num = md.getColumnCount();
        List listOfRows = new ArrayList();
        while (rs.next()) {
            Map mapOfColValues = new HashMap(num);
            for (int i = 1; i <= num; i++) {
                mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
            }
            listOfRows.add(mapOfColValues);
        }
        return listOfRows;
    }

    /**
     * 通用取结果方案,返回JSONArray
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    /*
    public static JSONArray extractJSONArray(ResultSet rs) throws SQLException {
        ResultSetMetaData md = rs.getMetaData();
        int num = md.getColumnCount();
        JSONArray array = new JSONArray();
        while (rs.next()) {
            JSONObject mapOfColValues = new JSONObject();
            for (int i = 1; i <= num; i++) {
                mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
            }
            array.add(mapOfColValues);
        }
        return array;
    }
    */

}

